﻿CREATE TABLE [dbo].[Customer] (
    [CustomerId] [int] NOT NULL IDENTITY,
    [Code] [nvarchar](10),
    [ContactName] [nvarchar](1024),
    [Address] [nvarchar](1024),
    [City] [nvarchar](1024),
    [Telephone] [nvarchar](255),
    [MobilePhone] [nvarchar](255),
    [Fax] [nvarchar](255),
    [Email] [nvarchar](255),
    [CustomerType] [int] NOT NULL DEFAULT 1,
    CONSTRAINT [PK_dbo.Customer] PRIMARY KEY ([CustomerId])
)
CREATE TABLE [dbo].[__MigrationHistory] (
    [MigrationId] [nvarchar](255) NOT NULL,
    [Model] [varbinary](max) NOT NULL,
    [ProductVersion] [nvarchar](32) NOT NULL,
    CONSTRAINT [PK_dbo.__MigrationHistory] PRIMARY KEY ([MigrationId])
)
BEGIN TRY
    EXEC sp_MS_marksystemobject 'dbo.__MigrationHistory'
END TRY
BEGIN CATCH
END CATCH;

ALTER TABLE [dbo].[Customer] ADD [IsAgent] [bit] NOT NULL DEFAULT 0
DECLARE @var0 nvarchar(128)
SELECT @var0 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.Customer')
AND col_name(parent_object_id, parent_column_id) = 'CustomerType';
IF @var0 IS NOT NULL
    EXECUTE('ALTER TABLE [dbo].[Customer] DROP CONSTRAINT ' + @var0)
ALTER TABLE [dbo].[Customer] DROP COLUMN [CustomerType]

CREATE TABLE [dbo].[CustomerType] (
    [CustomerTypeId] [int] NOT NULL IDENTITY,
    [Name] [nvarchar](1024),
    CONSTRAINT [PK_dbo.CustomerType] PRIMARY KEY ([CustomerTypeId])
)
Insert into CustomerType(Name) Values('Khách lẻ')
Insert into CustomerType(Name) Values('Khách buôn')
ALTER TABLE [dbo].[Customer] ADD [CustomerTypeId] [int] NOT NULL DEFAULT 1
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [FK_dbo.Customer_dbo.CustomerType_CustomerTypeId] FOREIGN KEY ([CustomerTypeId]) REFERENCES [dbo].[CustomerType] ([CustomerTypeId]) ON DELETE CASCADE
CREATE INDEX [IX_CustomerTypeId] ON [dbo].[Customer]([CustomerTypeId])
DECLARE @var0 nvarchar(128)
SELECT @var0 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.Customer')
AND col_name(parent_object_id, parent_column_id) = 'IsAgent';
IF @var0 IS NOT NULL
    EXECUTE('ALTER TABLE [dbo].[Customer] DROP CONSTRAINT ' + @var0)
ALTER TABLE [dbo].[Customer] DROP COLUMN [IsAgent]
DECLARE @var1 nvarchar(128)
SELECT @var1 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.Customer')
AND col_name(parent_object_id, parent_column_id) = 'IsPersonal';
IF @var1 IS NOT NULL
    EXECUTE('ALTER TABLE [dbo].[Customer] DROP CONSTRAINT ' + @var1)
ALTER TABLE [dbo].[Customer] DROP COLUMN [IsPersonal]

CREATE TABLE [dbo].[Manufacturer] (
    [ManufacturerId] [int] NOT NULL IDENTITY,
    [Name] [nvarchar](255),
    [Country] [nvarchar](255),
    CONSTRAINT [PK_dbo.Manufacturer] PRIMARY KEY ([ManufacturerId])
)

CREATE TABLE [dbo].[Vehicle] (
    [VehicleId] [int] NOT NULL IDENTITY,
    [Registration] [nvarchar](10) NOT NULL,
    [Model] [nvarchar](255),
    [Year] [int] NOT NULL,
    [Color] [nvarchar](25),
    [EngineSize] [nvarchar](25),
    [Doors] [int] NOT NULL,
    [Fuel] [nvarchar](255),
    [GearboxType] [nvarchar](255),
    [Gears] [int] NOT NULL,
    [EngineNumber] [nvarchar](255),
    [CylinderArrangement] [nvarchar](255),
    [Cylinders] [int] NOT NULL,
    [Bore] [float] NOT NULL,
    [Valves] [nvarchar](255),
    [ValveGear] [nvarchar](255),
    [Height] [float] NOT NULL,
    [Width] [float] NOT NULL,
    [Length] [float] NOT NULL,
    [ManufacturerId] [int] NOT NULL,
    [CustomerId] [int] NOT NULL,
    CONSTRAINT [PK_dbo.Vehicle] PRIMARY KEY ([VehicleId])
)
CREATE INDEX [IX_ManufacturerId] ON [dbo].[Vehicle]([ManufacturerId])
CREATE INDEX [IX_CustomerId] ON [dbo].[Vehicle]([CustomerId])
CREATE UNIQUE INDEX IX_Vehicle_Registration ON Vehicle (Registration)
ALTER TABLE [dbo].[Vehicle] ADD CONSTRAINT [FK_dbo.Vehicle_dbo.Manufacturer_ManufacturerId] FOREIGN KEY ([ManufacturerId]) REFERENCES [dbo].[Manufacturer] ([ManufacturerId]) ON DELETE CASCADE
ALTER TABLE [dbo].[Vehicle] ADD CONSTRAINT [FK_dbo.Vehicle_dbo.Customer_CustomerId] FOREIGN KEY ([CustomerId]) REFERENCES [dbo].[Customer] ([CustomerId]) ON DELETE CASCADE